import pandas as pd
import pymysql
from DBUtils.PooledDB import PooledDB

class universal_mysql:
    # 连接池参数设定
    def Connection_Pool_Settings(self, setting):
        """设置数据库连接池参数
        
        Args:
            setting: 包含数据库连接信息的字典
        """
        # 提取并验证连接参数
        Setting_ip = setting.get('host')
        Setting_port = setting.get('port')
        Setting_user = setting.get('user')
        Setting_password = setting.get('password')
        Setting_database = setting.get('database')

    # 连接池设定
    def mysql_connection(self, setting):
        """创建并返回数据库连接池
        
        Args:
            setting: 包含数据库连接信息的字典
                必须包含host, port, user, password, database键值对
                
        Returns:
            PooledDB: 配置好的数据库连接池对象
        """
        try:
            # 提取连接参数
            Setting_ip = setting['host']
            Setting_port = setting['port']
            Setting_user = setting['user']
            Setting_password = setting['password']
            Setting_database = setting['database']
            
            # 创建连接池
            pool = PooledDB(
                creator=pymysql,          # 使用的数据库驱动
                maxconnections=20,        # 最大连接数
                mincached=3,              # 初始化空闲连接数
                maxcached=2,              # 最大空闲连接数
                maxshared=3,              # 最大共享连接数
                blocking=True,            # 无可用连接时是否阻塞等待
                maxusage=None,            # 连接最大使用次数
                setsession=[],            # 会话初始化命令
                host=Setting_ip,
                port=Setting_port,
                user=Setting_user,
                password=Setting_password,
                database=Setting_database,
                charset='utf8mb4'         # 使用utf8mb4支持更多字符
            )
            return pool
        except KeyError as e:
            raise KeyError(f"连接设置中缺少必要参数: {e}")
        except Exception as e:
            raise RuntimeError(f"创建数据库连接池失败: {e}")

    # 从数据库读取数据
    def sql_reading(self, address, a, b, pool):
        """根据指定参数从数据库读取数据
        
        Args:
            address: 表名
            a: 读取行数或标识参数
            b: 起始位置或标识参数
            pool: 数据库连接池
                
        Returns:
            pd.DataFrame: 查询结果的数据框
        """
        conn = None
        try:
            conn = pool.connection()
            
            # 根据参数构建不同的查询语句
            if a == -1:
                sql_wp_testing = f"SELECT * FROM `{address}`"
            elif b == -1:
                sql_wp_testing = f"SELECT * FROM `{address}` LIMIT 0,{a}"
            else:
                sql_wp_testing = f"SELECT * FROM `{address}` LIMIT {b},{a}"
            
            # 执行查询并返回结果
            df_wp_testing = pd.read_sql(sql_wp_testing, con=conn)
            return df_wp_testing
        except Exception as e:
            raise RuntimeError(f"从数据库读取数据失败: {e}")
        finally:
            if conn:
                conn.close()

    # 按照筛选条件从数据库读取数据
    def sql_reading_condition(self, sql, pool):
        """执行自定义SQL查询并返回结果
        
        Args:
            sql: SQL查询语句
            pool: 数据库连接池
                
        Returns:
            pd.DataFrame: 查询结果的数据框
        """
        conn = None
        try:
            conn = pool.connection()
            df_wp_testing = pd.read_sql(sql, con=conn)
            return df_wp_testing
        except Exception as e:
            raise RuntimeError(f"执行条件查询失败: {e}")
        finally:
            if conn:
                conn.close()

    # 按照筛选条件从数据库读取数据(不使用pandas）
    def sql_reading_condition_nopandas(self, sql, pool):
        """执行自定义SQL查询并以元组形式返回结果（不使用pandas）
        
        Args:
            sql: SQL查询语句
            pool: 数据库连接池
                
        Returns:
            tuple: 查询结果的元组集合
        """
        conn = None
        cursor = None
        try:
            conn = pool.connection()
            cursor = conn.cursor()
            cursor.execute(sql)
            results = cursor.fetchall()
            return results
        except Exception as e:
            raise RuntimeError(f"执行非pandas查询失败: {e}")
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()

    # SQL语句输入数据库（高并发，数据连接池）
    def sql_input_database_pool(self, sql, pool):
        """执行SQL语句并提交到数据库
        
        Args:
            sql: SQL执行语句
            pool: 数据库连接池
        """
        conn = None
        cursor = None
        try:
            conn = pool.connection()
            cursor = conn.cursor()
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            if conn:
                conn.rollback()
            raise RuntimeError(f"执行SQL语句失败: {e}")
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()

    # 数据导入SQL模块（高并发，数据连接池）(pandas)
    def sql_learning_pool_add_pandas(self, df0, table_name, pool):
        """将pandas DataFrame数据插入到数据库表中
        
        Args:
            df0: 包含待插入数据的DataFrame
            table_name: 目标数据库表名
            pool: 数据库连接池
        """
        conn = None
        cursor = None
        try:
            conn = pool.connection()
            cursor = conn.cursor()
            
            # 获取列名并格式化
            columns = df0.columns.tolist()
            columns_str = ', '.join([f'`{col}`' for col in columns])
            
            # 批量插入数据，提高性能
            values_list = []
            for _, row in df0.iterrows():
                # 处理每个值，进行适当的转义
                values = []
                for val in row.values:
                    if pd.isna(val):
                        values.append('NULL')
                    elif isinstance(val, str):
                        # 转义字符串中的单引号
                        escaped_val = str(val).replace("'", "''")
                        values.append(f"'{escaped_val}'")
                    else:
                        values.append(str(val))
                
                values_str = ', '.join(values)
                values_list.append(f'({values_str})')
            
            # 构建批量插入SQL语句
            sql = f"INSERT INTO `{table_name}` ({columns_str}) VALUES {', '.join(values_list)}"
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            if conn:
                conn.rollback()
            raise RuntimeError(f"向数据库插入数据失败: {e}")
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()

    # 数据导入SQL模块（高并发，数据连接池）(pandas)(update)
    def sql_learning_pool_update_pandas(self, df0, table_name, pool, judgment_object):
        """根据判断条件更新或插入数据
        
        Args:
            df0: 包含待更新/插入数据的DataFrame
            table_name: 目标数据库表名
            pool: 数据库连接池
            judgment_object: 判断字段列表，用于确定是更新还是插入
        """
        conn = None
        cursor = None
        
        try:
            # 尝试读取现有数据
            df1 = self.sql_reading(table_name, -1, 0, pool)
            
            # 如果表为空，则直接插入数据
            if df1.empty:
                self.sql_learning_pool_add_pandas(df0, table_name, pool)
                return
            
            conn = pool.connection()
            cursor = conn.cursor()
            
            columns = df0.columns.tolist()
            columns_str = ', '.join([f'`{col}`' for col in columns])
            
            # 处理每一行数据
            for _, row in df0.iterrows():
                # 构建WHERE条件
                where_conditions = []
                match_found = True
                
                for judgment_col in judgment_object:
                    if judgment_col not in columns:
                        raise ValueError(f"判断字段 '{judgment_col}' 不在DataFrame中")
                    
                    # 获取当前行的判断字段值
                    judgment_value = row[judgment_col]
                    
                    # 检查该值是否在现有数据中
                    if not (df1[judgment_col] == judgment_value).any():
                        match_found = False
                        break
                    
                    # 添加到WHERE条件
                    if pd.isna(judgment_value):
                        where_conditions.append(f'`{judgment_col}` IS NULL')
                    else:
                        # 转义字符串值
                        if isinstance(judgment_value, str):
                            judgment_value = judgment_value.replace("'", "''")
                            where_conditions.append(f"`{judgment_col}` = '{judgment_value}'")
                        else:
                            where_conditions.append(f"`{judgment_col}` = {judgment_value}")
                
                # 如果找到匹配项，执行更新操作
                if match_found:
                    # 构建SET子句
                    set_clauses = []
                    for col in columns:
                        if col in judgment_object:
                            continue  # 跳过判断字段
                        
                        val = row[col]
                        if pd.isna(val):
                            set_clauses.append(f'`{col}` = NULL')
                        elif isinstance(val, str):
                            escaped_val = str(val).replace("'", "''")
                            set_clauses.append(f"`{col}` = '{escaped_val}'")
                        else:
                            set_clauses.append(f"`{col}` = {val}")
                    
                    if set_clauses:  # 确保有需要更新的字段
                        sql_update = f"UPDATE `{table_name}` SET {', '.join(set_clauses)} WHERE {' AND '.join(where_conditions)}"
                        cursor.execute(sql_update)
                else:
                    # 未找到匹配项，执行插入操作
                    values = []
                    for val in row.values:
                        if pd.isna(val):
                            values.append('NULL')
                        elif isinstance(val, str):
                            escaped_val = str(val).replace("'", "''")
                            values.append(f"'{escaped_val}'")
                        else:
                            values.append(str(val))
                    
                    values_str = ', '.join(values)
                    sql_insert = f"INSERT INTO `{table_name}` ({columns_str}) VALUES ({values_str})"
                    cursor.execute(sql_insert)
            
            conn.commit()
        except Exception as e:
            if conn:
                conn.rollback()
            raise RuntimeError(f"更新/插入数据库数据失败: {e}")
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()
    
    # 补充数据模块（高并发，数据连接池）(pandas)
    def sql_learning_pool_supplement_pandas(self, df0, table_name, pool):
        """补充数据到数据库表中
        
        Args:
            df0: 包含待补充数据的DataFrame
            table_name: 目标数据库表名
            pool: 数据库连接池
        """
        try:
            # 尝试读取现有数据
            df1 = self.sql_reading(table_name, -1, 0, pool)
            
            # 如果表为空，则直接插入数据
            if df1.empty:
                self.sql_learning_pool_add_pandas(df0, table_name, pool)
            # 表不为空时，这里可以扩展为更复杂的补充逻辑
            # 当前实现保留原有行为，仅在表为空时插入数据
        except Exception as e:
            raise RuntimeError(f"补充数据失败: {e}")

    # MySQL中SELECT * 替代工具------------------------------------------------------------
    def sql_replace_mysql(self, list1):
        """将列表转换为SQL列名字符串，用于替代SELECT *
        
        Args:
            list1: 列名列表
                
        Returns:
            str: 格式化后的列名字符串
        """
        if not list1:
            return ''
        
        # 格式化列名，添加反引号防止关键字冲突
        formatted_columns = [f'`{str(col)}`' for col in list1]
        return ', '.join(formatted_columns)